import sqlite3
import sys
import threading

from PySide2.QtCore import QObject, Signal, Slot, QThread, QEventLoop
from PySide2.QtWidgets import QApplication, QWidget, QVBoxLayout, QLabel, QPushButton


# 定义数据库管理器类
class DatabaseManager(QObject):
    def __init__(self, db_path):
        super().__init__()
        self.db_path = db_path
        self.conn = None
        self._connections = {}

    # 连接到数据库
    def connect(self):
        self.conn = sqlite3.connect(self.db_path)

    # 关闭数据库连接
    def close(self):
        if self.conn:
            self.conn.close()

    # 创建表的槽函数
    @Slot(str, str)
    def create_table(self, table_name, columns):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            sql = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns})"
            # 执行创建表操作
            cursor.execute(sql)
            # 提交事务
            conn.commit()
        except Exception as e:
            print(f"Error creating table: {e}")
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 插入数据的槽函数
    @Slot(str, list, str)
    def insert(self, table_name, values, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造占位符
            placeholders = ', '.join(['?'] * len(values))
            # 构造 SQL 语句
            if table_name == "users":
                sql = f"INSERT INTO {table_name} (username, avatar, seq, nickname, remarkname) VALUES ({placeholders})"
            elif table_name == "message":
                sql = f"INSERT INTO {table_name} (username, is_outgoing, content, time) VALUES ({placeholders})"
            # 执行插入操作
            cursor.execute(sql, values)
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 批量插入数据的槽函数
    @Slot(str, list, str)
    def bulk_insert(self, table_name, values_list, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造占位符
            placeholders = ', '.join(['?'] * len(values_list[0]))
            # 构造 SQL 语句
            if table_name == "users":
                sql = f"INSERT INTO {table_name} (username, avatar, seq, nickname, remarkname) VALUES ({placeholders})"
            elif table_name == "message":
                sql = f"INSERT INTO {table_name} (username, is_outgoing, content, time) VALUES ({placeholders})"
            # 执行批量插入操作
            cursor.executemany(sql, values_list)
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 更新数据的槽函数
    @Slot(str, str, str, str, str, str)
    def update(self, table_name, columns, values, where_column, where_value, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()

            # 如果 columns 是单个字符串，转换为列表
            if isinstance(columns, str):
                columns = [columns]

            # 如果 values 是单个值，转换为列表
            if not isinstance(values, (list, tuple)):
                values = [values]

            # 构造 SET 子句
            set_clause = ", ".join([f"{col} = ?" for col in columns])

            # 构造 SQL 语句
            sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_column} = ?"

            # # 构造 SQL 语句
            # sql = f"UPDATE {table_name} SET {column} = ? WHERE {where_column} = ?"
            # 执行更新操作
            cursor.execute(sql, (*values, where_value))
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            print("update error:{}".format(e))
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 更新指定 seq 的记录的槽函数
    @Slot(str, list, str, str)
    def update_by_seq(self, table_name, new_values, seq_column, seq_value, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            columns = ['username', 'avatar', 'seq', 'nickname', 'remarkname']
            # 构造 SET 子句
            set_clause = ", ".join([f"{col} = ?" for col in columns])
            where_clause = f"{seq_column} = ?"
            sql = f"UPDATE {table_name} SET {set_clause} WHERE {where_clause}"
            # 构造参数列表
            params = new_values + [seq_value]
            # 执行更新操作
            cursor.execute(sql, params)
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            print("update error{}".format(e))
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 删除数据的槽函数
    @Slot(str, str, str, str, str)
    def delete(self, table_name, where_column, where_value, extra_conditions=None, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            sql = f"DELETE FROM {table_name} WHERE {where_column} = ?"
            if extra_conditions:
                sql += f" AND {extra_conditions}"
            # 执行删除操作
            cursor.execute(sql, (where_value,))
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 删除多余的消息，保留最近的50条消息
    def delete_old_messages(self, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            sql = f"""
            DELETE FROM message
            WHERE (username, time) NOT IN (
                SELECT username, time
                FROM (
                    SELECT username, time
                    FROM message
                    ORDER BY username, time DESC
                    LIMIT 50
                ) AS latest_messages
            )
            """
            # 执行删除操作
            cursor.execute(sql)
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 查询数据的槽函数
    @Slot(str, str, str, str, str)
    def select(self, table_name, columns, where_column, where_value, distinct=False, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            distinct_str = "DISTINCT " if distinct else ""
            sql = f"SELECT {distinct_str}{columns} FROM {table_name}"
            if where_column and where_value:
                sql += f" WHERE {where_column} = ?"
            # print(f"Executing query: {sql}")
            # 执行查询操作
            if where_column and where_value:
                cursor.execute(sql, (where_value,))
            else:
                cursor.execute(sql)
            # 获取所有结果
            result = cursor.fetchall()
            # print(result)
            # 如果有回调函数，则调用它并传递查询结果
            return result
            # if callable(callback):
            #     callback(True, result)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, None, str(e))
            # print(f"Error executing query: {e}")  # 添加日志输出
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 删除表的槽函数
    @Slot(str, str)
    def drop_table(self, table_name, callback=None):
        try:
            # 获取当前线程的连接
            conn = self.get_connection()
            # 获取游标
            cursor = conn.cursor()
            # 构造 SQL 语句
            sql = f"DROP TABLE IF EXISTS {table_name}"
            # 执行删除表操作
            cursor.execute(sql)
            # 提交事务
            conn.commit()
            # 如果有回调函数，则调用它
            if callable(callback):
                callback(True)
        except Exception as e:
            # 如果发生异常，则调用回调函数并传递错误信息
            if callable(callback):
                callback(False, str(e))
        finally:
            # 关闭当前线程的连接
            self.close_connection(conn)

    # 获取当前线程的连接
    def get_connection(self):
        # 检查当前线程是否有连接
        thread_id = threading.current_thread().ident
        if not hasattr(self, '_connections'):
            self._connections = {}
        if thread_id not in self._connections:
            # 如果没有，则创建一个新的连接
            self._connections[thread_id] = sqlite3.connect(self.db_path)
        return self._connections[thread_id]

    # 关闭当前线程的连接
    def close_connection(self, conn):
        # 获取当前线程的 ID
        thread_id = threading.current_thread().ident
        # 关闭连接
        conn.close()
        # 从字典中移除连接
        if thread_id in self._connections:
            del self._connections[thread_id]

    def close_all_connections(self):
        for thread_id, conn in self._connections.items():
            conn.close()
        self._connections.clear()


# 定义数据库工作线程类
class DatabaseWorker(QThread):
    # 定义一个信号，用于通知主线程操作是否成功及任何错误信息
    finished = Signal(bool, str)
    select_finished = Signal(bool, list, str)

    def __init__(self, db_manager: DatabaseManager):
        # 初始化父类
        super().__init__()
        # 设置数据库管理器实例
        self.db_manager = db_manager
        # 操作类型
        self.action = None
        # 表名
        self.table_name = None
        # 值列表
        self.values = None
        # 列名
        self.column = None
        # 新值
        self.value = None
        # 条件列名
        self.where_column = None
        # 条件值
        self.where_value = None
        # 查询列名
        self.columns = None
        # 回调函数
        self.callback = None
        # 设置新的值
        self.new_values = None
        # seq 字段名
        self.seq_column = None
        # seq 值
        self.seq_value = None
        # select 搜索到的值
        self.select_result = None
        self.distinct = None
        self.values_list = None
        self._stop_event = threading.Event()

        # 插入数据的槽函数
    @Slot(str, list, str)
    def do_insert(self, table_name, values, callback=None):
        # 设置操作类型
        self.action = 'insert'
        # 设置表名
        self.table_name = table_name
        # 设置值列表
        self.values = values
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 批量插入数据的槽函数
    @Slot(str, list, str)
    def do_bulk_insert(self, table_name, values_list, callback=None):
        # 设置操作类型
        self.action = 'bulk_insert'
        # 设置表名
        self.table_name = table_name
        # 设置值列表
        self.values_list = values_list
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 更新数据的槽函数
    @Slot(str, str, str, str, str, str)
    def do_update(self, table_name, column, value, where_column, where_value, callback=None):
        # 设置操作类型
        self.action = 'update'
        # 设置表名
        self.table_name = table_name
        # 设置列名
        self.column = column
        # 设置新值
        self.value = value
        # 设置条件列名
        self.where_column = where_column
        # 设置条件值
        self.where_value = where_value
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 更新指定 seq 的记录的槽函数
    @Slot(str, list, str, str, str)
    def do_update_by_seq(self, table_name, new_values, seq_column, seq_value, callback=None):
        # 设置操作类型
        self.action = 'update_by_seq'
        # 设置表名
        self.table_name = table_name
        # 设置新的值
        self.new_values = new_values
        # 设置 seq 字段名
        self.seq_column = seq_column
        # 设置 seq 值
        self.seq_value = seq_value
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 删除数据的槽函数
    @Slot(str, str, str, str, str)
    def do_delete(self, table_name, where_column, where_value, callback=None):
        # 设置操作类型
        self.action = 'delete'
        # 设置表名
        self.table_name = table_name
        # 设置条件列名
        self.where_column = where_column
        # 设置条件值
        self.where_value = where_value
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 查询数据的槽函数
    @Slot(str, str, str, str, str)
    def do_select(self, table_name, columns, where_column, where_value, distinct=False, callback=None):
        # 设置操作类型
        self.action = 'select'
        # 设置表名
        self.table_name = table_name
        # 设置查询列名
        self.columns = columns
        # 设置条件列名
        self.where_column = where_column
        # 设置条件值
        self.where_value = where_value
        self.distinct = distinct
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()
        self.wait()
        return self.select_result

    # 删除表的槽函数
    @Slot(str, str)
    def do_drop_table(self, table_name, callback=None):
        # 设置操作类型
        self.action = 'drop_table'
        # 设置表名
        self.table_name = table_name
        # 设置回调函数
        self.callback = callback
        # 启动线程
        self.start()

    # 删除表多余的历史数据
    def do_delete_old_messages(self):
        # 设置操作类型
        self.action = 'delete_old_messages'
        # 启动线程
        self.start()

    def stop(self):
        self._stop_event.set()

    # 线程运行函数
    def run(self):
        if self._stop_event.is_set():
            return
        # 根据操作类型执行相应的数据库操作
        if self.action == 'insert':
            self.db_manager.insert(self.table_name, self.values, self._on_operation_complete)
        elif self.action == 'update':
            self.db_manager.update(self.table_name, self.column, self.value, self.where_column, self.where_value,
                                   self._on_operation_complete)
        elif self.action == 'delete':
            self.db_manager.delete(self.table_name, self.where_column, self.where_value, self._on_operation_complete)
        elif self.action == 'select':
            self.select_result = self.db_manager.select(self.table_name, self.columns, self.where_column,
                                                        self.where_value,
                                                        self.distinct, self._on_select_operation_complete)
        elif self.action == 'drop_table':
            self.db_manager.drop_table(self.table_name, self._on_operation_complete)
        elif self.action == 'update_by_seq':
            self.db_manager.update_by_seq(self.table_name, self.new_values, self.seq_column, self.seq_value,
                                          self._on_operation_complete)
        elif self.action == 'delete_old_messages':
            self.db_manager.delete_old_messages()
        elif self.action == 'bulk_insert':
            self.db_manager.bulk_insert(self.table_name, self.values_list, self._on_operation_complete)

    # 操作完成后调用的槽函数
    def _on_operation_complete(self, success, error_message=None):
        # 发射信号，通知主线程操作是否成功及任何错误信息
        # self.finished.emit(success, error_message)
        pass

    def _on_select_operation_complete(self, success, result, error_message=None):
        # 发射信号，通知主线程操作是否成功及任何错误信息
        # self.select_finished.emit(success, result, error_message)
        pass



